In a typical enterprise installation, application software as well as the database schema used by the application is maintained for many years without fresh reinstallations. Upgrades are applied to the software and to the supporting database schema to keep the application up-to-date with the latest version produced by the software vendor. Unlike with the software where new versions of the programs simply replace the old ones upon upgrade, upgrading the database schema is much more delicate. The database schema upgrade scripts need to ensure that the upgraded database schema matches exactly the expectations of the relevant version of software application, not only in the database object definitions, but also for content of database tables, especially for entities that are essential for the correct operation of the application software.
When developing the database schema upgrade scripts, it is essential to validate that when applied to the old database schema, the result matches the database schema is created by fresh installation. Conventionally, an iterative approach is used when comparing content of a newly installed database schema (also referred to as a reference) with the content of an old database schema, upgraded with the database schema upgrade scripts to the latest version. If the content is the same, then the database schema upgrade scripts are validated. On the other hand, if the content is not the same, then the database schema upgrade scripts have to be checked and additional instructions, such as INSERT, UPDATE, etc., may have to be added. As database schema is highly normalized, with extensive use of static lookup tables and dynamic look up tables (which may be referred to as registry tables), the same entity may be represented with records with different synthetic primary identifiers in each schema, depending on the order of actions that have been made in the system and upgrade history of the application and its database schema. This yields a large number of false positives in the output of conventional database schema validation tools. Currently, these false positives have to be processed manually.